﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using webapp4U.BOL;

namespace webapp4U.DAL
{
    public class FileDAL : BaseDAL
    {

        private File ObjectIDataReader(IDataReader dr)
        {
            File obj = new File();
            obj.FileID = (dr["FileID"] is DBNull) ? int.MinValue : (int)dr["FileID"];
            obj.CateID = (dr["CateID"] is DBNull) ? int.MinValue : (int)dr["CateID"];
            obj.FileName = (dr["FileName"] is DBNull) ? string.Empty : (string)dr["FileName"];
            obj.Width = (dr["Width"] is DBNull) ? int.MinValue : (int)dr["Width"];
            obj.Height = (dr["Height"] is DBNull) ? int.MinValue : (int)dr["Height"];
            obj.FileSize = (dr["FileSize"] is DBNull) ? int.MinValue : (int)dr["FileSize"];
            obj.Title = (dr["Title"] is DBNull) ? string.Empty : (string)dr["Title"];
            obj.Description = (dr["Description"] is DBNull) ? string.Empty : (string)dr["Description"];
            obj.TotalViews = (dr["TotalViews"] is DBNull) ? int.MinValue : (int)dr["TotalViews"];
            obj.PostDate = (dr["PostDate"] is DBNull) ? DateTime.MinValue : (DateTime)dr["PostDate"];
            obj.Username = (dr["Username"] is DBNull) ? string.Empty : (string)dr["Username"];
            return obj;
        }


        // co phan trang nen them obj.RecordID
        private File ObjectIDataReader_Paging(IDataReader dr)
        {
            File obj = new File();
            obj.RecordID = (dr["ID"] is DBNull) ? int.MinValue : (int)dr["ID"];
            obj.FileID = (dr["FileID"] is DBNull) ? int.MinValue : (int)dr["FileID"];
            obj.CateID = (dr["CateID"] is DBNull) ? int.MinValue : (int)dr["CateID"];
            obj.FileName = (dr["FileName"] is DBNull) ? string.Empty : (string)dr["FileName"];
            obj.Width = (dr["Width"] is DBNull) ? int.MinValue : (int)dr["Width"];
            obj.Height = (dr["Height"] is DBNull) ? int.MinValue : (int)dr["Height"];
            obj.FileSize = (dr["FileSize"] is DBNull) ? int.MinValue : (int)dr["FileSize"];
            obj.Title = (dr["Title"] is DBNull) ? string.Empty : (string)dr["Title"];
            obj.Description = (dr["Description"] is DBNull) ? string.Empty : (string)dr["Description"];
            obj.TotalViews = (dr["TotalViews"] is DBNull) ? int.MinValue : (int)dr["TotalViews"];
            obj.PostDate = (dr["PostDate"] is DBNull) ? DateTime.MinValue : (DateTime)dr["PostDate"];
            obj.Username = (dr["Username"] is DBNull) ? string.Empty : (string)dr["Username"];
            return obj;
        }


        // dem so file theo CateID
        public int GetFileCountByCateID(int CateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                try
                {
                    int value = 0;
                    SqlCommand myCommand = new SqlCommand("Photo_File_GetFileCountByCateID", myConn);
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;
                    value = Convert.ToInt32(myCommand.ExecuteScalar());
                    myConn.Close();
                    return (value);
                }
                catch { return 0; }
            }
        }


        // lay ra mot CateID ngau nhien theo ParentCateID
        // ma CateID nay phai chua it nhat 1 file 
        public int GetRandomCateIDByParentCateID(int ParentCateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                try
                {
                    int value = 0;
                    SqlCommand myCommand = new SqlCommand("Photo_GetRandomCateIDByParentCateID", myConn);
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.Add("@ParentCateID", SqlDbType.Int).Value = ParentCateID;
                    value = Convert.ToInt32(myCommand.ExecuteScalar());
                    myConn.Close();
                    return (value);
                }
                catch { return 0; }
            }
        }


        // lay thu muc chua file
        public string GetAppKeyByFileID(int FileID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                try
                {
                    string value = "";
                    SqlCommand myCommand = new SqlCommand("Photo_File_GetAppKeyByFileID", myConn);
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.Add("@FileID", SqlDbType.Int).Value = FileID;
                    value = (string)myCommand.ExecuteScalar();
                    myConn.Close();
                    return value;
                }
                catch
                { return ""; }
            }
        }


        public List<File> GetFileSlideShowByCateID(int CateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                List<File> list = new List<File>();
                SqlCommand myCommand = new SqlCommand("Photo_File_GetFileSlideShowByCateID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;
                SqlDataReader dr = myCommand.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(ObjectIDataReader(dr));
                    }
                    dr.Close();
                    myConn.Close();
                }
                return list;
            }
        }


        public List<File> GetFileByCateID(int CateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                List<File> list = new List<File>();
                SqlCommand myCommand = new SqlCommand("Photo_File_GetFileByCateID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;
                SqlDataReader dr = myCommand.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(ObjectIDataReader(dr));
                    }
                    dr.Close();
                    myConn.Close();
                }
                return list;
            }
        }


        // lay file theo Category, co phan trang
        public List<File> GetFileByCateID_Paging(int CateID, int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                List<File> list = new List<File>();
                SqlCommand myCommand = new SqlCommand("Photo_File_GetFileByCate_Paging", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(ObjectIDataReader_Paging(dr));
                    }
                    dr.Close();
                    myConn.Close();
                }
                return list;
            }
        }


        public File GetFileByFileID(int FileID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                File obj = new File();
                SqlCommand myCommand = new SqlCommand("Photo_File_GetFileByFileID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@FileID", SqlDbType.Int).Value = FileID;
                SqlDataReader dr = myCommand.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        obj = ObjectIDataReader(dr);
                    }
                    dr.Close();
                    myConn.Close();
                }
                return obj;
            }
        }


        public int Insert(File obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Photo_File_Insert", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@FileID", SqlDbType.Int).Value = obj.FileID;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = obj.CateID;
                myCommand.Parameters.Add("@FileName", SqlDbType.VarChar).Value = obj.FileName;
                myCommand.Parameters.Add("@Width", SqlDbType.Int).Value = obj.Width;
                myCommand.Parameters.Add("@Height", SqlDbType.Int).Value = obj.Height;
                myCommand.Parameters.Add("@FileSize", SqlDbType.Int).Value = obj.FileSize;
                myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
                myCommand.Parameters.Add("@Description", SqlDbType.NVarChar).Value = obj.Description;
                myCommand.Parameters.Add("@TotalViews", SqlDbType.Int).Value = obj.TotalViews;
                myCommand.Parameters.Add("@PostDate", SqlDbType.DateTime).Value = obj.PostDate;
                myCommand.Parameters.Add("@Username", SqlDbType.VarChar).Value = obj.Username;
                myCommand.ExecuteNonQuery();
                myConn.Close();
                return 1;
            }
        }


        //public int Update(File obj)
        //{
        //    using (SqlConnection myConn = GetConnectionString())
        //    {
        //        SqlCommand myCommand = new SqlCommand("Photo_File_Update", myConn);
        //        myCommand.CommandType = CommandType.StoredProcedure;
        //        myCommand.Parameters.Add("@FileID", SqlDbType.Int).Value = obj.FileID;
        //        myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = obj.CateID;
        //        myCommand.Parameters.Add("@FileName", SqlDbType.VarChar).Value = obj.FileName;
        //        myCommand.Parameters.Add("@Width", SqlDbType.Int).Value = obj.Width;
        //        myCommand.Parameters.Add("@Height", SqlDbType.Int).Value = obj.Height;
        //        myCommand.Parameters.Add("@FileSize", SqlDbType.Int).Value = obj.FileSize;
        //        myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
        //        myCommand.Parameters.Add("@Description", SqlDbType.NVarChar).Value = obj.Description;
        //        myCommand.Parameters.Add("@TotalViews", SqlDbType.Int).Value = obj.TotalViews;
        //        myCommand.Parameters.Add("@PostDate", SqlDbType.DateTime).Value = obj.PostDate;
        //        myCommand.Parameters.Add("@Username", SqlDbType.VarChar).Value = obj.Username;
        //        myCommand.ExecuteNonQuery();
        //        myConn.Close();
        //        return obj.CateID;
        //    }
        //}

        public int Update(int FileID, string Title)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Photo_File_Update", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@FileID", SqlDbType.Int).Value = FileID;
                myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = Title;
                myCommand.ExecuteNonQuery();
                myConn.Close();
                return FileID;
            }
        }

        public int Delete(int FileID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Photo_File_Delete", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@FileID", SqlDbType.Int).Value = FileID;
                myCommand.ExecuteNonQuery();
                myConn.Close();
                return FileID;
            }
        }


	}

}
